/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sfoutils;


import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
 *
 * @author rejun001
 * @deprecated 
 */
public class DBConnector
{

    String gidsUser;
    String gidsPassword;
    Connection gidsConn = null;
    String GIDSenv;
    //String quest_id = "7A2501C3-0BCF-AF69-E040-18ACC560415C";  // number 1
    //String quest_id = "7A2501C3-08BC-AF69-E040-18ACC560415C";  // number 2
    //String quest_id = "7A2501C3-0BDE-AF69-E040-18ACC560415C";  // number 3
    //String quest_id = "7A2501C3-0C00-AF69-E040-18ACC560415C";  // number 4
    String quest_id = "7A2501C3-0C78-AF69-E040-18ACC560415C";  // number 5
    ResultSet rs = null;
    Statement stmt = null;
    Clob clob;
    Blob jpegBlob;
    String clobStr = null;


    public Clob getClob()
    {
        return clob;
    }


    public DBConnector( String user , String pw , String env )
    {
        System.out.println( "In DBConnector constructor" );
        gidsUser = user;
        gidsPassword = pw;
        GIDSenv = env;
        gidsConn = getGidsConnection( gidsConn );

        if ( gidsConn == null )
        {
            System.out.println( "Unable to get connection to GIDs Database" );
            //logger.fatal("Unable to get connection to GIDs Database");
            System.exit( 1 );
        }
    }


    public long getClobFromDB( SfoType type )
    {
        System.out.println( "in go()" );
        long clen = 0;
        String col = null;
        switch ( type )
        {
            case ITEM_LAYOUT:
                col = "item_layout";
                break;
            case DESIGNER_SFO:
                col = "designer_sfo";
                break;
            case ENCAPSULATED_SFO:
                col = "encapsulated_sfo";
                break;
            default:
                break;
        }
        try
        {
            String sql = "select " + col + " from custom_content_layouts where form_media = 'PAPER' and quest_id = '" + quest_id + "'";

            stmt = gidsConn.createStatement();
            rs = stmt.executeQuery( sql );

            while ( rs.next() )
            {
                clob = rs.getClob( col );
                clen = clob.length();
                System.out.println( col + " Clob length is " + clen );
                //System.out.println("Clob String is: /" + clobStr + "/");                
            }
        } catch ( SQLException sqle )
        {
            //logger.fatal(ex.getMessage(), ex);
            //appendToEmail("Caught SQLException "+ex.getMessage());
            sqle.printStackTrace();
            System.exit( 1 );
        } catch ( Exception ex )
        {
            //logger.fatal(ex.getMessage(), ex);
            //appendToEmail("Caught Exception "+ex.getMessage());
            ex.printStackTrace();
            System.exit( 1 );
        } finally
        {
            try
            {
                rs.close();
                stmt.close();
                //gidsConn.close();
                //logger.debug("got all records from GIDS");
            } catch ( SQLException sqle )
            {
                //logger.error(ex.getMessage(), ex);
                //appendToEmail("Caught SQLException "+ex.getMessage());
                sqle.getStackTrace();
            }
            return clen;

        }
    }


    public long getClobFromDB()
    {
        long clen = 0;

        try
        {
            String sql = "select designer_sfo from custom_content_layouts where quest_id = '" + quest_id + "'";

            stmt = gidsConn.createStatement();
            rs = stmt.executeQuery( sql );

            while ( rs.next() )
            {
                clob = rs.getClob( "DESIGNER_SFO" );
                clen = clob.length();

                System.out.println( "DESIGNER_SFO Clob length is " + clen );
                //System.out.println("Clob String is: /" + clobStr + "/");

            }

        } catch ( SQLException sqle )
        {
            //logger.fatal(ex.getMessage(), ex);
            //appendToEmail("Caught SQLException "+ex.getMessage());
            sqle.printStackTrace();
            System.exit( 1 );
        } catch ( Exception ex )
        {
            //logger.fatal(ex.getMessage(), ex);
            //appendToEmail("Caught Exception "+ex.getMessage());
            ex.printStackTrace();
            System.exit( 1 );
        } finally
        {
            try
            {
                rs.close();
                stmt.close();
                //gidsConn.close();
                //logger.debug("got all records from GIDS");
            } catch ( SQLException sqle )
            {
                //logger.error(ex.getMessage(), ex);
                //appendToEmail("Caught SQLException "+ex.getMessage());
                sqle.getStackTrace();
            }
            return clen;

        }
    }


    public Blob getJpegFromDB()
    {
        try
        {
            String sql = "select item_layout_jpeg from custom_content_layouts where form_media = 'PAPER' and quest_id = '" + quest_id + "'";

            stmt = gidsConn.createStatement();
            rs = stmt.executeQuery( sql );

            while ( rs.next() )
            {
                jpegBlob = rs.getBlob( "ITEM_LAYOUT_JPEG" );
            }

        } catch ( SQLException sqle )
        {
            //logger.fatal(ex.getMessage(), ex);
            //appendToEmail("Caught SQLException "+ex.getMessage());
            sqle.printStackTrace();
            System.exit( 1 );
        } catch ( Exception ex )
        {
            //logger.fatal(ex.getMessage(), ex);
            //appendToEmail("Caught Exception "+ex.getMessage());
            ex.printStackTrace();
            System.exit( 1 );
        } finally
        {
            try
            {
                rs.close();
                stmt.close();
                //gidsConn.close();
                //logger.debug("got all records from GIDS");
            } catch ( SQLException sqle )
            {
                //logger.error(ex.getMessage(), ex);
                //appendToEmail("Caught SQLException "+ex.getMessage());
                sqle.getStackTrace();
            }
            return jpegBlob;

        }
    }


    private Connection getGidsConnection( Connection gidsConn )
    {
        String gidsURL = null;
        if ( GIDSenv.equalsIgnoreCase( "test" ) )
        {
            // EQCTSA -- doesn't work
            gidsURL = "jdbc:oracle:thin:@(DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = orarac4-1v1.test.econ.census.gov)(PORT = 1521))"
                    + "(ADDRESS = (PROTOCOL = TCP)(HOST = orarac4-1v1.test.econ.census.gov)(PORT = 1521))"
                    + "(LOAD_BALANCE = yes)"
                    + "(CONNECT_DATA ="
                    + "(SERVER = DEDICATED)"
                    + "(SERVICE_NAME = eqctsa)"
                    + "(FAILOVER_MODE ="
                    + "(TYPE = select)"
                    + "(METHOD = basic)"
                    + "(RETRIES = 3)"
                    + "(DELAY = 5))))";
        } else if ( GIDSenv.equalsIgnoreCase( "prod" ) )
        {
            gidsURL = "jdbc:oracle:thin:@(DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = orarac3-1v1.econ.census.gov)(PORT = 1521))"
                    + "(ADDRESS = (PROTOCOL = TCP)(HOST = orarac3-2v1.econ.census.gov)(PORT = 1521))"
                    + "(LOAD_BALANCE = yes)"
                    + "(CONNECT_DATA ="
                    + "(SERVER = DEDICATED)"
                    + //                    "(SID = eqcprd1)"+
                    "(SERVICE_NAME = eqcprd)"
                    + "(FAILOVER_MODE ="
                    + "(TYPE = select)"
                    + "(METHOD = basic)"
                    + "(RETRIES = 3)"
                    + "(DELAY = 5))))";

        } else if ( GIDSenv.equalsIgnoreCase( "dev" ) )
        {
            gidsURL = "jdbc:oracle:thin:@(DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = orarac2-1v1.dev.econ.census.gov)(PORT = 1521))"
                    + "(ADDRESS = (PROTOCOL = TCP)(HOST = orarac2-1v1.dev.econ.census.gov)(PORT = 1521))"
                    + "(LOAD_BALANCE = yes)"
                    + "(CONNECT_DATA ="
                    + "(SERVER = DEDICATED)"
                    + //                    "(SID = eqcprd1)"+
                    "(SERVICE_NAME = eqcprd)"
                    + "(FAILOVER_MODE ="
                    + "(TYPE = select)"
                    + "(METHOD = basic)"
                    + "(RETRIES = 3)"
                    + "(DELAY = 5))))";

        } else
        {
            //logger.fatal("Environment "+GIDSenv+" not yet supported");
            System.out.println( "Environment " + GIDSenv + " not yet supported" );
            System.exit( 1 );
        }

        try
        {
            Class.forName( "oracle.jdbc.driver.OracleDriver" );
            gidsConn = DriverManager.getConnection( gidsURL , gidsUser , gidsPassword );
        } catch ( ClassNotFoundException ex )
        {
            //logger.fatal(ex.getMessage(), ex);
            ex.printStackTrace();
            System.exit( 1 );
        } catch ( SQLException ex )
        {
            //logger.fatal(ex.getMessage(), ex);
            System.out.println( ex.getMessage() );
            ex.getStackTrace();
            System.exit( 1 );
        }
        return gidsConn;
    }


    public void closeGidsConnection()
    {
        try
        {
            gidsConn.close();
        } catch ( SQLException sqle )
        {
            System.out.println( sqle.getMessage() );
            sqle.printStackTrace();
            System.exit( 1 );
        }

    }


    public String getSfoClobString( Clob clob , long clen )
    {
        String cs = null;
        char[] chars = new char[ (int) clen + 100 ];
        try
        {
            Reader reader = clob.getCharacterStream();
            int rlen = reader.read( chars );
            cs = new String( chars );
        } catch ( Exception e )
        {
            e.printStackTrace();
            System.out.println( e.getMessage() );
        }

        return cs;
    }

}
